/** FASSIT (Forms of Selective Attention in Intelligent Transportation Systems)
 * Computational Creativity and Digital Media
 * Cognitive and Media Systems Group
 * Centre for Informatics and Systems of the University of Coimbra (CISUC)
 *
 * Copyright (c) 2010-2013 University of Coimbra, Portugal
 * All rights reserved.
 */
package androidLayer.Mysql;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import poi.Info.POI;
import poi.Info.POIwithSAMetrics;
import poi.Surprise.SurpriseDaysOff;
import poi.Surprise.SurprisePrice;
import poi.Surprise.SurpriseSchedule;
import poi.Uncertainty.UncertaintyDaysOff;
import poi.Uncertainty.UncertaintyPrice;
import poi.Uncertainty.UncertaintySchedule;

import agent.Context.Context;
import agent.Context.Goal;
import agent.Metrics.Surprise.FirstPoiSurprise;
import agent.Metrics.Surprise.PastSurpriseValues;
import agent.Metrics.Uncertainty.FirstPoiUncertainty;
import agent.Metrics.Uncertainty.PastUncertaintyValues;

import com.mysql.jdbc.Connection;

import functionsVariables.MySQLConnection;

/**
 * 
 * 
 * @author Hernani Costa
 * 
 * @version
 */
public class MysqlConnection implements Serializable{
	private static final long serialVersionUID = 1L;
	private MySQLConnection dbconnection;
	private Connection connection;

	/**
	 * ------------------------------------------------------------------------
	 * Constructor class
	 * ------------------------------------------------------------------------
	 */
	public MysqlConnection() {
		dbconnection = MySQLConnection.getInstance();
	}

	/**
	 * ------------------------------------------------------------------------
	 * Creates an user model to the new user
	 * ------------------------------------------------------------------------
	 * 
	 * @param email
	 *            - user email
	 * @return true if the user model was been well created, false otherwise
	 */
	public boolean createUserModel(String email) {
		connection = dbconnection.openConnection();
		String sql1 = " CREATE TABLE ex4_" + email + "_model LIKE POIsDB.ex4_agent_model";
		String sql2 = " INSERT ex4_" + email + "_model SELECT * FROM POIsDB.ex4_agent_model";
		String sql3 = " CREATE TABLE ex4_" + email + "_sa_values LIKE POIsDB.ex4_agent_sa_values";
		String sql4 = " INSERT ex4_" + email + "_sa_values SELECT * FROM POIsDB.ex4_agent_sa_values;";
		String sql5 = " CREATE TABLE ex4_" + email + "_evaluation LIKE POIsDB.ex4_evaluation";

		PreparedStatement s;
		try {
			s = connection.prepareStatement(sql1);
			s.executeUpdate();
			s = connection.prepareStatement(sql2);
			s.executeUpdate();
			s = connection.prepareStatement(sql3);
			s.executeUpdate();
			s = connection.prepareStatement(sql4);
			s.executeUpdate();
			s = connection.prepareStatement(sql5);
			s.executeUpdate();
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
			dbconnection.closeConnection();
			return false;
		}

		dbconnection.closeConnection();
		return true;
	}

	/**
	 * ------------------------------------------------------------------------
	 * Verifies if an user is already registered in the systems or not
	 * ------------------------------------------------------------------------
	 * 
	 * @param email
	 *            - user email
	 * @param password
	 *            - user password
	 * @return - true if the user is already registered in the systems (table
	 *         ex4_users); false otherwise
	 */
	public boolean verifyUser(String email, String password) {
		boolean flag = false;
		Statement stt = null;
		ResultSet rs = null;
		String user = "", pass = "";
		try {
			connection = dbconnection.openConnection();
			stt = connection.createStatement();
			rs = stt.executeQuery("SELECT * FROM ex4_users WHERE email='" + email + "' AND pass='" + password + "'");

			while (rs.next()) {
				user = rs.getString(1);
				pass = rs.getString(2);
				flag = true;
			}
			dbconnection.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			dbconnection.closeConnection();
			flag = false;
			return flag;
		}
		return flag;
	}

	/**
	 * ------------------------------------------------------------------------
	 * Removes the '@' and the '.' from the email
	 * ------------------------------------------------------------------------
	 * 
	 * @param email
	 *            - user email
	 * @return the email without '@' and '.'
	 */
	public String encodeEmail(String email) {
		String temp = email.replace("@", "___");
		temp = temp.replace(".", "__");
		return temp;

	}

	/**
	 * ------------------------------------------------------------------------
	 * Adds the '@' and the '.' to the email
	 * ------------------------------------------------------------------------
	 * 
	 * @param string
	 *            - string with the user email
	 * @return the email with '@' and '.'
	 */
	public String decodeEmail(String string) {
		String temp = string.replace("___", "@");
		temp = temp.replace("__", ".");
		return temp;

	}


	/**
	 * Adds the new user to the table ex4_users
	 * @param email - user email
	 * @param password - user password
	 */
	public void addUser(String email, String password){
		PreparedStatement s;
		String sql="";
		try {
			connection = dbconnection.openConnection();
			sql="INSERT INTO ex4_users(email, pass) VALUES('" + email + "','" + password + "')";
			s = connection.prepareStatement(sql);
			s.executeUpdate();
			s.close();
			dbconnection.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			dbconnection.closeConnection();
		}
	}
	
	public void saveUserFeedback(){
		saveUserEvaluation();
		//saveSAValues(email, poiWithSelectiveAttentionValues, context)
	}
	
	private boolean saveUserEvaluation(){
	
		return true;
	}
	
	/**
	 * Saves the Selective Attention values for all the parameters (Price,
	 * DaysOff and Schedule)
	 * 
	 * @param poiWithSelectiveAttentionValues
	 *            - POI with the Selective Attention values
	 * @param context
	 *            - Context
	 * @return true if successfully saved, false if not
	 */
	private boolean saveSAValues(String email, POIwithSAMetrics poiWithSelectiveAttentionValues, Context context) {
		int poiID;
		int runID = context.getRunID();
		String agentGoal = context.getGoal();
		double s_price_cheap, s_price_average, s_price_expensive, s_schedule_morning, s_schedule_afternoon, s_schedule_night, s_dayoff_sunday, s_dayoff_monday, s_dayoff_tuesday, s_dayoff_wednesday, s_dayoff_thursday, s_dayoff_friday, s_dayoff_saturday;
		double u_price_cheap, u_price_average, u_price_expensive, u_schedule_morning, u_schedule_afternoon, u_schedule_night, u_dayoff_sunday, u_dayoff_monday, u_dayoff_tuesday, u_dayoff_wednesday, u_dayoff_thursday, u_dayoff_friday, u_dayoff_saturday;
		POI poi = null;

		connection = dbconnection.openConnection();
		try {
			PreparedStatement s = null;

			poi = poiWithSelectiveAttentionValues.getPoi();

			poiID = poi.getPoiID();

			// Surprise Values
			s_price_cheap = poiWithSelectiveAttentionValues.getSurprisePrice()
					.getCheap();
			s_price_average = poiWithSelectiveAttentionValues
					.getSurprisePrice().getAverage();
			s_price_expensive = poiWithSelectiveAttentionValues
					.getSurprisePrice().getExpensive();
			s_schedule_morning = poiWithSelectiveAttentionValues
					.getSurpriseSchedule().getMorning();
			s_schedule_afternoon = poiWithSelectiveAttentionValues
					.getSurpriseSchedule().getAfternoon();
			s_schedule_night = poiWithSelectiveAttentionValues
					.getSurpriseSchedule().getNight();
			s_dayoff_sunday = poiWithSelectiveAttentionValues
					.getSurpriseDayOff().getSunday();
			s_dayoff_monday = poiWithSelectiveAttentionValues
					.getSurpriseDayOff().getMonday();
			s_dayoff_tuesday = poiWithSelectiveAttentionValues
					.getSurpriseDayOff().getTuesday();
			s_dayoff_wednesday = poiWithSelectiveAttentionValues
					.getSurpriseDayOff().getWednesday();
			s_dayoff_thursday = poiWithSelectiveAttentionValues
					.getSurpriseDayOff().getThursday();
			s_dayoff_friday = poiWithSelectiveAttentionValues
					.getSurpriseDayOff().getFriday();
			s_dayoff_saturday = poiWithSelectiveAttentionValues
					.getSurpriseDayOff().getSaturday();

			// Uncertainty Values
			u_price_cheap = poiWithSelectiveAttentionValues
					.getUncertaintyPrice().getCheap();
			u_price_average = poiWithSelectiveAttentionValues
					.getUncertaintyPrice().getAverage();
			u_price_expensive = poiWithSelectiveAttentionValues
					.getUncertaintyPrice().getExpensive();
			u_schedule_morning = poiWithSelectiveAttentionValues
					.getUncertaintySchedule().getMorning();
			u_schedule_afternoon = poiWithSelectiveAttentionValues
					.getUncertaintySchedule().getAfternoon();
			u_schedule_night = poiWithSelectiveAttentionValues
					.getUncertaintySchedule().getNight();
			u_dayoff_sunday = poiWithSelectiveAttentionValues
					.getUncertaintyDayOff().getSunday();
			u_dayoff_monday = poiWithSelectiveAttentionValues
					.getUncertaintyDayOff().getMonday();
			u_dayoff_tuesday = poiWithSelectiveAttentionValues
					.getUncertaintyDayOff().getTuesday();
			u_dayoff_wednesday = poiWithSelectiveAttentionValues
					.getUncertaintyDayOff().getWednesday();
			u_dayoff_thursday = poiWithSelectiveAttentionValues
					.getUncertaintyDayOff().getThursday();
			u_dayoff_friday = poiWithSelectiveAttentionValues
					.getUncertaintyDayOff().getFriday();
			u_dayoff_saturday = poiWithSelectiveAttentionValues
					.getUncertaintyDayOff().getSaturday();

			s = connection
					.prepareStatement("INSERT INTO ex4_" + email + "_sa_values ("
							+ "poiID, "
							+ "s_price_cheap,"
							+ "s_price_average,"
							+ "s_price_expensive,"
							+ "s_schedule_morning,"
							+ "s_schedule_afternoon,"
							+ "s_schedule_night,"
							+ "s_dayoff_sunday, "
							+ "s_dayoff_monday,"
							+ "s_dayoff_tuesday, "
							+ "s_dayoff_wednesday, "
							+ "s_dayoff_thursday,"
							+ "s_dayoff_friday,"
							+ "s_dayoff_saturday,"

							+ "u_price_cheap,"
							+ "u_price_average,"
							+ "u_price_expensive,"
							+ "u_schedule_morning,"
							+ "u_schedule_afternoon,"
							+ "u_schedule_night,"
							+ "u_dayoff_sunday, "
							+ "u_dayoff_monday,"
							+ "u_dayoff_tuesday, "
							+ "u_dayoff_wednesday, "
							+ "u_dayoff_thursday,"
							+ "u_dayoff_friday,"
							+ "u_dayoff_saturday,"
							+ "runID,"
							+ "agentGoal) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

			s.setInt(1, poiID);
			s.setDouble(2, s_price_cheap);
			s.setDouble(3, s_price_average);
			s.setDouble(4, s_price_expensive);
			s.setDouble(5, s_schedule_morning);
			s.setDouble(6, s_schedule_afternoon);
			s.setDouble(7, s_schedule_night);
			s.setDouble(8, s_dayoff_sunday);
			s.setDouble(9, s_dayoff_monday);
			s.setDouble(10, s_dayoff_tuesday);
			s.setDouble(11, s_dayoff_wednesday);
			s.setDouble(12, s_dayoff_thursday);
			s.setDouble(13, s_dayoff_friday);
			s.setDouble(14, s_dayoff_saturday);

			s.setDouble(15, u_price_cheap);
			s.setDouble(16, u_price_average);
			s.setDouble(17, u_price_expensive);
			s.setDouble(18, u_schedule_morning);
			s.setDouble(19, u_schedule_afternoon);
			s.setDouble(20, u_schedule_night);
			s.setDouble(21, u_dayoff_sunday);
			s.setDouble(22, u_dayoff_monday);
			s.setDouble(23, u_dayoff_tuesday);
			s.setDouble(24, u_dayoff_wednesday);
			s.setDouble(25, u_dayoff_thursday);
			s.setDouble(26, u_dayoff_friday);
			s.setDouble(27, u_dayoff_saturday);

			s.setDouble(28, runID);
			s.setString(29, agentGoal);

			s.executeUpdate();
			s.close();

			dbconnection.closeConnection();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			dbconnection.closeConnection();
			return false;
		}
	}

	/**
	 * For a specific Goal, returns all the Surprise values for all the POIs
	 * visited by the Agent in the past
	 * 
	 * @param email
	 *            - user email
	 * 
	 * @param goal
	 *            - Current context
	 * @return all the Surprise values for all the POIs visited by the Agent in
	 *         the past
	 */
	@SuppressWarnings("null")
	public PastSurpriseValues getSurpriseValuesForGoal(String email, String goal) {
		Statement stt = null;
		ResultSet rs = null;
		PastSurpriseValues surprise = new PastSurpriseValues();
		SurpriseDaysOff sDayOff = null;
		SurprisePrice sPrice = null;
		SurpriseSchedule sSchedule = null;
		double price_cheap, price_average, price_expensive, schedule_morning, schedule_afternoon, schedule_night, dayoff_sunday, dayoff_monday, dayoff_tuesday, dayoff_wednesday, dayoff_thursday, dayoff_friday, dayoff_saturday;

		int id, poiID, runID;
		String agentGoal = "";

		try {
			connection = dbconnection.openConnection();
			stt = connection.createStatement();
			rs = stt.executeQuery("SELECT * FROM ex4_" + email + "_sa_values WHERE agentGoal='" + goal + "'");

			while (rs.next()) {
				id = rs.getInt(1);
				poiID = rs.getInt(2);
				price_cheap = rs.getDouble(3);
				price_average = rs.getDouble(4);
				price_expensive = rs.getDouble(5);
				schedule_morning = rs.getDouble(6);
				schedule_afternoon = rs.getDouble(7);
				schedule_night = rs.getDouble(8);
				dayoff_sunday = rs.getDouble(9);
				dayoff_monday = rs.getDouble(10);
				dayoff_tuesday = rs.getDouble(11);
				dayoff_wednesday = rs.getDouble(12);
				dayoff_thursday = rs.getDouble(13);
				dayoff_friday = rs.getDouble(14);
				dayoff_saturday = rs.getDouble(15);
				runID = rs.getInt(16);
				agentGoal = rs.getString(17);

				sDayOff = new SurpriseDaysOff(dayoff_sunday, dayoff_monday, dayoff_tuesday, dayoff_wednesday, dayoff_thursday, dayoff_friday, dayoff_saturday);
				sPrice = new SurprisePrice(price_cheap, price_average, price_expensive);
				sSchedule = new SurpriseSchedule(schedule_morning, schedule_afternoon, schedule_night);
				surprise.addSurprise(sDayOff, sPrice, sSchedule);
			}
			dbconnection.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			dbconnection.closeConnection();
			return null;
		}
		return surprise;
	}

	/**
	 * For a specific Goal, returns all the Uncertainty values for all the POIs
	 * visited by the Agent in the past
	 * 
	 * @param email
	 *            - user email
	 * 
	 * @param goal
	 *            - Current context
	 * @return all the Uncertainty values for all the POIs visited by the Agent
	 *         in the past
	 */
	@SuppressWarnings("null")
	public PastUncertaintyValues getUncertaintyValuesForGoal(String email, String goal) {
		Statement stt = null;
		ResultSet rs = null;
		PastUncertaintyValues uncertainty = new PastUncertaintyValues();
		UncertaintyDaysOff uDayOff = null;
		UncertaintyPrice uPrice = null;
		UncertaintySchedule uSchedule = null;
		double price_cheap, price_average, price_expensive, schedule_morning, schedule_afternoon, schedule_night, dayoff_sunday, dayoff_monday, dayoff_tuesday, dayoff_wednesday, dayoff_thursday, dayoff_friday, dayoff_saturday;

		int id, poiID, runID;
		String agentGoal = "";

		try {
			connection = dbconnection.openConnection();
			stt = connection.createStatement();
			rs = stt.executeQuery("SELECT * FROM ex4_" + email + "_sa_values WHERE agentGoal='" + goal + "'");

			while (rs.next()) {
				id = rs.getInt(1);
				poiID = rs.getInt(2);
				price_cheap = rs.getDouble(3);
				price_average = rs.getDouble(4);
				price_expensive = rs.getDouble(5);
				schedule_morning = rs.getDouble(6);
				schedule_afternoon = rs.getDouble(7);
				schedule_night = rs.getDouble(8);
				dayoff_sunday = rs.getDouble(9);
				dayoff_monday = rs.getDouble(10);
				dayoff_tuesday = rs.getDouble(11);
				dayoff_wednesday = rs.getDouble(12);
				dayoff_thursday = rs.getDouble(13);
				dayoff_friday = rs.getDouble(14);
				dayoff_saturday = rs.getDouble(15);
				runID = rs.getInt(16);
				agentGoal = rs.getString(17);

				uDayOff = new UncertaintyDaysOff(dayoff_sunday, dayoff_monday, dayoff_tuesday, dayoff_wednesday, dayoff_thursday, dayoff_friday,
						dayoff_saturday);
				uPrice = new UncertaintyPrice(price_cheap, price_average, price_expensive);
				uSchedule = new UncertaintySchedule(schedule_morning, schedule_afternoon, schedule_night);
				uncertainty.addUncertainty(uDayOff, uPrice, uSchedule);

			}
			dbconnection.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			dbconnection.closeConnection();
			return null;
		}
		return uncertainty;
	}

	

	
	/**
	 * ------------------------------------------------------------------------
	 * Used to provide an example that exercises the functionality of the class.
	 * ------------------------------------------------------------------------
	 * 
	 * @param args
	 */
	public static void main(String args[]) {
		MysqlConnection c = new MysqlConnection();
		String email = "hpcosta@dei.uc.pt";
		String pass = "costa";

		email = c.encodeEmail(email);

		boolean exists = c.verifyUser(email, pass);
		if (exists) {
			// load model
			System.out.println("nao faz nada");
		} else {
			c.addUser(email, pass);
			c.createUserModel(email);
			//
		}

		System.out.println(email);

	}
	// CREATE TABLE ex4_users LIKE POIsDB.ex3_users; INSERT ex4_users SELECT *
	// FROM POIsDB.ex3_users;

}
